﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class comprovanteAulas : System.Web.UI.Page
    {
        public List<string> horaInicialT = new List<string>();
        public List<string> horaFinalT = new List<string>();
        public List<string> professorT = new List<string>();
        public List<string> alunoT = new List<string>();
        public List<string> grupoT = new List<string>();
        public List<string> disciplinaT = new List<string>();
        public List<string> codigoAulaT = new List<string>();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                // Inserindo a data atual do computador
                txtData.Text = DateTime.Now.ToString("dd/MM/yyyy");
                gerarRelatorio();
            }
        }

        protected void btnListar_Click(object sender, EventArgs e)
        {
            gerarRelatorio();
        }

        void gerarRelatorio()
        {
            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            string selectAula = "";

            //selectAula = " SELECT codigoAula, codigoProfessor, codigoAluno, codigoGrupo, dataAula, horaInicialAula, horaFinalAula, codigoDisciplina, descricaoAssuntoAula, observacoesAula,";
            //selectAula += " aulaRealizada, ";
            //selectAula += " (SELECT nomeProfessor FROM professor ";
            //selectAula += " WHERE (codigoProfessor = aula.codigoProfessor)) AS nomeProfessor, ";
            //selectAula += " (SELECT nomeAluno FROM aluno ";
            //selectAula += " WHERE(codigoAluno = aula.codigoAluno)) AS nomeAluno, ";
            //selectAula += " (SELECT nomeGrupo FROM grupo ";
            //selectAula += " WHERE(codigoGrupo = aula.codigoGrupo)) AS nomeGrupo, ";
            //selectAula += " (SELECT descricaoDisciplina FROM disciplina ";
            //selectAula += " WHERE (codigoDisciplina = aula.codigoDisciplina)) AS descricaoDisciplina ";
            //selectAula += " FROM aula WHERE (aulaRealizada = 1) and dataAula = '" + txtData.Text + "'";

            selectAula = "SELECT codigoAula, codigoProfessor, codigoAluno, codigoGrupo, dataAula, horaInicialAula, horaFinalAula, codigoDisciplina, descricaoAssuntoAula, observacoesAula,  ";
            selectAula += "aulaRealizada, valorHora, valorTotalSemDesconto, valorTotalComDesconto, falta, codigoAnoLetivo, linha, meiaHora, ";
            selectAula += " (SELECT nomeProfessor FROM professor WHERE (codigoProfessor = aula.codigoProfessor)) AS nomeProfessor, ";
            selectAula += " (SELECT nomeAluno FROM aluno WHERE(codigoAluno = aula.codigoAluno)) AS nomeAluno, ";
            selectAula += " (SELECT nomeGrupo FROM grupo WHERE(codigoGrupo = aula.codigoGrupo)) AS nomeGrupo, ";
            selectAula += " (SELECT descricaoDisciplina FROM disciplina WHERE (codigoDisciplina = aula.codigoDisciplina)) AS descricaoDisciplina, ";
            selectAula += " (SELECT COUNT(*) AS qtd FROM aula as a WHERE (aulaRealizada = 1) and (a.codigoProfessor = aula.codigoProfessor) AND (a.codigoAluno = aula.codigoAluno) AND (a.dataAula = aula.dataAula) and (a.aulaRealizada = 1) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) as quantidadeAulaA, ";

            selectAula += " (SELECT COUNT(*) AS qtd FROM aula as a WHERE (aulaRealizada = 1) and (a.codigoProfessor = aula.codigoProfessor) AND (a.codigoGrupo = aula.codigoGrupo) AND (a.dataAula = aula.dataAula) and (a.aulaRealizada = 1) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) as quantidadeAulaG, ";
            selectAula += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS a WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoAluno = aula.codigoAluno) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaInicialIndividual, ";
            selectAula += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aa WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoAluno = aula.codigoAluno) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaFinalIndividual, ";
            selectAula += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS a WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoGrupo = aula.codigoGrupo) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaInicialGrupo, ";
            selectAula += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aa WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoGrupo = aula.codigoGrupo) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaFinalGrupo ";
            selectAula += "FROM aula WHERE (aulaRealizada = 1) AND (dataAula = '" + txtData.Text + "') ";
            //selectAula += " ORDER BY horaInicialAula";

            SqlCommand cmdAula = new SqlCommand(selectAula, sqlc);
            SqlDataReader drAula = cmdAula.ExecuteReader();

            string relatorio = "<table width=100% border=1 cellpadding=5 cellspacing=0 bordercolor=#99CCFF style=\"font-family:Verdana; font-size:10px\"/>";
            relatorio += "<tr>";
            relatorio += " <td width=10% class=textoNegro><strong>Data da Aula</strong></td>";
            relatorio += " <td width=10% class=textoNegro><strong>Aula</strong></td>";
            relatorio += " <td width=10% class=textoNegro><strong>Professor</strong></td>";
            relatorio += " <td width=10% class=textoNegro><strong>Aluno</strong></td>";
            relatorio += " <td width=10% class=textoNegro><strong>Grupo</strong></td>";
            relatorio += " <td width=10% class=textoNegro><strong>Hora Inicial</strong></td>";
            relatorio += " <td width=10% class=textoNegro><strong>Hora Final</strong></td>";
            relatorio += " <td width=10% class=textoNegro><strong>Escolher Aula</strong></td>";
            relatorio += " </tr>";

            //string descricaoDisciplina = "";
            string dataAula = "";
            string codigoAula = "";
            string descricaoDisciplina = "";
            string nomeProfessor = "";
            string nomeAluno = "";
            string nomeGrupo = "";
            string codigoGrupo = "";
            string horaInicialAula = "";
            string horaFinalAula = "";
            string codigoDisciplina = "";

            string codigoProfessorAluno = "";
            int contador = 0;

            if (drAula.HasRows)
            {
                while (drAula.Read())
                {
                    if (contador % 2 == 0)
                    {


                        //if (codigoProfessorAluno != drAula["codigoProfessor"].ToString() + drAula["codigoAluno"].ToString() + drAula["codigoGrupo"].ToString())
                        //{
                        dataAula = drAula["dataAula"].ToString();
                        codigoAula = drAula["codigoAula"].ToString();
                        nomeProfessor = drAula["nomeProfessor"].ToString();
                        nomeAluno = drAula["nomeAluno"].ToString();
                        nomeGrupo = drAula["nomeGrupo"].ToString();
                        codigoGrupo = drAula["codigoGrupo"].ToString();
                        codigoDisciplina = drAula["codigoDisciplina"].ToString();

                        if (codigoGrupo == "")
                        {
                            horaInicialAula = drAula["horaInicialIndividual"].ToString();
                            horaFinalAula = drAula["horaFinalIndividual"].ToString();
                        }
                        else
                        {
                            horaInicialAula = drAula["horaInicialGrupo"].ToString();
                            horaFinalAula = drAula["horaFinalGrupo"].ToString();
                        }

                        descricaoDisciplina = drAula["descricaoDisciplina"].ToString();


                        //AQUI CONTEUDO DO RELATÓRIO//
                        //relatorio += "<tr>";
                        //relatorio += "<td>" + dataAula + "</td>";
                        //relatorio += "<td>" + descricaoDisciplina + "</td>";
                        //relatorio += "<td>" + nomeProfessor + "</td>";
                        //relatorio += "<td>" + nomeAluno + "</td>";
                        //relatorio += "<td>" + nomeGrupo + "</td>";
                        //relatorio += "<td>" + horaInicialAula + "</td>";
                        //relatorio += "<td>" + horaFinalAula + "</td>";

                        //relatorio += "<td><input type=checkbox name=" + codigoAula + " value=" + codigoAula + " ></td>";//checked

                        //relatorio += "</td>";
                        //relatorio += "</tr>";
                        //} 

                        horaInicialT.Add(horaInicialAula);
                        horaFinalT.Add(horaFinalAula);
                        professorT.Add(nomeProfessor);
                        alunoT.Add(nomeAluno);
                        grupoT.Add(nomeGrupo);
                        disciplinaT.Add(descricaoDisciplina);
                        codigoAulaT.Add(codigoAula);

                    }


                    TimeSpan qtdAula = Convert.ToDateTime(horaFinalAula).Subtract(Convert.ToDateTime(horaInicialAula));

                    double quantidadeAula = qtdAula.Hours;
                    if (qtdAula.Minutes == 30)
                    {
                        quantidadeAula += 0.5;
                    }

                    if (contador % 2 == 0)// && qtdAula.Hours == 2 || quantidadeAula == 2)
                    {
                        if (quantidadeAula == 2 || quantidadeAula == 1.5)
                        {
                            codigoProfessorAluno = drAula["codigoProfessor"].ToString() + drAula["codigoAluno"].ToString() + drAula["codigoGrupo"].ToString();
                            contador++;
                        }

                        else
                        {
                            codigoProfessorAluno = "";
                            contador = 0;
                        }


                    }
                    else
                    {
                        codigoProfessorAluno = "";
                        contador = 0;
                    }

                }

            }


            drAula.Close();
            cmdAula.Dispose();
            
            ordernar();

            int icount = 0;
            foreach (var item in codigoAulaT)
            {
                relatorio += "<tr>";
                relatorio += "<td>" + dataAula + "</td>";
                relatorio += "<td>" + disciplinaT[icount] + "</td>";
                relatorio += "<td>" + professorT[icount] + "</td>";
                relatorio += "<td>" + alunoT[icount] + "</td>";
                relatorio += "<td>" + grupoT[icount] + "</td>";
                relatorio += "<td>" + horaInicialT[icount] + "</td>";
                relatorio += "<td>" + horaFinalT[icount] + "</td>";

                relatorio += "<td><input type=checkbox name=" + item + " value=" + item + " ></td>";//checked

                relatorio += "</td>";
                relatorio += "</tr>";

                icount++;
            }

            relatorio += "</table>";

            Panel1.Controls.Add(new LiteralControl(relatorio));

            //fechar conexao
            sqlc.Close();
        }

        protected void btnGerarC_Click(object sender, EventArgs e)
        {
            List<string> lista = new List<string>();
            List<string> horaInicial = new List<string>();
            List<string> horaFinal = new List<string>();
            double quantidadeAula = 0;
            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            string selectAula = "";

            selectAula = "SELECT codigoAula, codigoProfessor, codigoAluno, codigoGrupo, dataAula, horaInicialAula, horaFinalAula, codigoDisciplina, descricaoAssuntoAula, observacoesAula,  ";
            selectAula += "aulaRealizada, valorHora, valorTotalSemDesconto, valorTotalComDesconto, falta, codigoAnoLetivo, linha, meiaHora, ";
            selectAula += " (SELECT nomeProfessor FROM professor WHERE (codigoProfessor = aula.codigoProfessor)) AS nomeProfessor, ";
            selectAula += " (SELECT nomeAluno FROM aluno WHERE(codigoAluno = aula.codigoAluno)) AS nomeAluno, ";
            selectAula += " (SELECT nomeGrupo FROM grupo WHERE(codigoGrupo = aula.codigoGrupo)) AS nomeGrupo, ";
            selectAula += " (SELECT descricaoDisciplina FROM disciplina WHERE (codigoDisciplina = aula.codigoDisciplina)) AS descricaoDisciplina, ";
            selectAula += " (SELECT COUNT(*) AS qtd FROM aula as a WHERE (aulaRealizada = 1) and (a.codigoProfessor = aula.codigoProfessor) AND (a.codigoAluno = aula.codigoAluno) AND (a.dataAula = aula.dataAula) and (a.aulaRealizada = 1) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) as quantidadeAulaA, ";

            selectAula += " (SELECT COUNT(*) AS qtd FROM aula as a WHERE (aulaRealizada = 1) and (a.codigoProfessor = aula.codigoProfessor) AND (a.codigoGrupo = aula.codigoGrupo) AND (a.dataAula = aula.dataAula) and (a.aulaRealizada = 1) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) as quantidadeAulaG, ";
            selectAula += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS a WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoAluno = aula.codigoAluno) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaInicialIndividual, ";
            selectAula += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aa WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoAluno = aula.codigoAluno) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaFinalIndividual, ";
            selectAula += "(SELECT MIN(horaInicialAula) AS horaInicial FROM aula AS a WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoGrupo = aula.codigoGrupo) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaInicialGrupo, ";
            selectAula += "(SELECT MAX(horaFinalAula) AS horaFinal FROM aula AS aa WHERE  (aulaRealizada = 1) and (codigoProfessor = aula.codigoProfessor) AND (codigoGrupo = aula.codigoGrupo) AND (dataAula = aula.dataAula) and codigoAula between aula.codigoAula and (aula.codigoAula + 1)) AS horaFinalGrupo ";
            selectAula += "FROM aula WHERE (aulaRealizada = 1) AND (dataAula = '" + txtData.Text + "') ";
            selectAula += " ORDER BY horaInicialIndividual, horaInicialGrupo ";

            SqlCommand cmdAula = new SqlCommand(selectAula, sqlc);
            SqlDataReader drAula = cmdAula.ExecuteReader();

            string codigoProfessorAluno = "";
            int contador = 0;

            if (drAula.HasRows)
            {
                while (drAula.Read())
                {
                    if (Request[drAula["codigoAula"].ToString()] == drAula["codigoAula"].ToString())
                    {
                        if (codigoProfessorAluno != drAula["codigoProfessor"].ToString() + drAula["codigoAluno"].ToString() + drAula["codigoGrupo"].ToString())
                        {
                            if (drAula["codigoGrupo"].ToString() == "")
                            {
                                if (drAula["horaInicialIndividual"].ToString() != "")
                                {
                                    horaInicial.Add(drAula["horaInicialIndividual"].ToString());
                                    lista.Add(drAula["codigoAula"].ToString());
                                }
                                if (drAula["horaFinalIndividual"].ToString() != "")
                                {
                                    horaFinal.Add(drAula["horaFinalIndividual"].ToString());
                                }

                                quantidadeAula = Convert.ToDouble(drAula["quantidadeAulaA"].ToString());
                            }
                            else
                            {
                                if ((drAula["horaInicialGrupo"].ToString() != ""))
                                {
                                    horaInicial.Add(drAula["horaInicialGrupo"].ToString());
                                    lista.Add(drAula["codigoAula"].ToString());
                                }
                                if ((drAula["horaFinalGrupo"].ToString() != ""))
                                {
                                    horaFinal.Add(drAula["horaFinalGrupo"].ToString());
                                }

                                quantidadeAula = Convert.ToDouble(drAula["quantidadeAulaG"].ToString());
                            }
                        }
                    }
                    contador++;

                    //TimeSpan qtdAula = Convert.ToDateTime(horaFinalAula).Subtract(Convert.ToDateTime(horaInicialAula));

                    if (contador % 2 == 0)// && qtdAula.Hours == 2)
                    {
                        codigoProfessorAluno = drAula["codigoProfessor"].ToString() + drAula["codigoAluno"].ToString() + drAula["codigoGrupo"].ToString();
                    }
                    else
                    {
                        codigoProfessorAluno = "";
                        contador = 0;
                    }
                }
            }

            drAula.Close();
            cmdAula.Dispose();
            sqlc.Close();

            emitirComprovantes(lista, horaInicial, horaFinal, txtData.Text);
        }

        void emitirComprovantes(List<string> lista, List<string> horaInicial, List<string> horaFinal, string data)
        {
            Panel1.Controls.Clear();

            validarValores vV = new validarValores();

            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();
            SqlConnection sqlf = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlf.Open();

            string comprovante = "";
            int cont = 1;
            string cor = "";
            string classe = "";

            int i = 0;

            foreach (var item in lista)
            {
                if (cont == 1)
                {
                    comprovante += "<div style=\"page-break-before:always\">";
                    cor = "#0033CC";
                    classe = "azul";
                }

                if (cont == 2)
                {
                    cor = "#006400";
                    classe = "verd";
                }

                if (cont == 3)
                {
                    cor = "#FF0000";
                    classe = "verm";
                }

                DateTime date = new DateTime(Convert.ToInt16(data.Substring(6, 4)), Convert.ToInt16(data.Substring(3, 2)), Convert.ToInt16(data.Substring(0, 2)));//(Convert.ToInt16(data.Substring(6, 4)), Convert.ToInt16(data.Substring(0, 2)), Convert.ToInt16(data.Substring(3, 2))); //29/05/2009               //TROCA DATA
                CultureInfo culture = new CultureInfo("pt-BR");
                DateTimeFormatInfo dtfi = culture.DateTimeFormat;



                if (dtfi.GetDayName(date.DayOfWeek) == "segunda-feira")
                {
                    cor = "#00008b";

                }

                if (dtfi.GetDayName(date.DayOfWeek) == "terça-feira")
                {
                    cor = "#458b00";

                }

                if (dtfi.GetDayName(date.DayOfWeek) == "quarta-feira")
                {
                    cor = "#ff0000";

                }

                if (dtfi.GetDayName(date.DayOfWeek) == "quinta-feira")
                {
                    cor = "#a020f0";

                }

                if (dtfi.GetDayName(date.DayOfWeek) == "sexta-feira")
                {
                    cor = "#a52a2a";

                }

                if (dtfi.GetDayName(date.DayOfWeek) == "sábado")
                {
                    cor = "#bebebe";

                }

                if (dtfi.GetDayName(date.DayOfWeek) == "domingo")
                {
                    cor = "#06adc1";

                }



                string sqlAulas = "SELECT codigoAula, codigoProfessor, codigoAluno, codigoGrupo, dataAula, horaInicialAula, horaFinalAula, ";
                sqlAulas += "codigoDisciplina, descricaoAssuntoAula, observacoesAula,  ";
                sqlAulas += "aulaRealizada, valorHora, valorTotalSemDesconto, valorTotalComDesconto, falta, codigoAnoLetivo, ";
                sqlAulas += "(SELECT nomeAluno FROM aluno WHERE (codigoAluno = aula.codigoAluno)) AS aluno, ";
                sqlAulas += "(SELECT nomeProfessor FROM professor WHERE (codigoProfessor = aula.codigoProfessor)) AS professor, ";
                sqlAulas += "(SELECT descricaoDisciplina FROM disciplina WHERE (codigoDisciplina = aula.codigoDisciplina)) AS disciplina ";
                sqlAulas += "FROM aula where codigoAula = " + item;

                SqlCommand cmdAula = new SqlCommand(sqlAulas, sqlc);
                SqlDataReader drAula = cmdAula.ExecuteReader();


                if (drAula.HasRows)
                {
                    drAula.Read();

                    TimeSpan qtdAula = Convert.ToDateTime(horaFinal[i]).Subtract(Convert.ToDateTime(horaInicial[i]));

                    double quantidadeAula = qtdAula.Hours;
                    if (qtdAula.Minutes == 30)
                    {
                        quantidadeAula += 0.5;
                    }

                    // TimeSpan x = Convert.ToDateTime(horaFinal[i]).Subtract(Convert.ToDateTime(horaInicial[i]));

                    string a = horaFinal[i].ToString();

                    //TimeSpan dif = Convert.ToDateTime(horaFinal[i]).Subtract(Convert.ToDateTime(horaInicial[i]));



                    // try
                    //{
                    //  dif = Convert.ToDateTime(horaFinal[i]).Subtract(Convert.ToDateTime(horaInicial[i]));
                    //}
                    // catch (InvalidCastException e){


                    //Response.Write("hora inicial :" + horaInicial[i].ToString() + "<br>");

                    // Response.Write("hora inicial :" + Convert.ToDateTime(horaInicial[i].ToString()) + "<br>");

                    //  Response.Write("hora final :" + Convert.ToDateTime(horaFinal[i].ToString()) + "<br>");

                    //  Response.Write("subtraido :" + Convert.ToDateTime(horaFinal[i]).Subtract(Convert.ToDateTime(horaInicial[i])) + "<br>");

                    // }           


                    //dif = 

                    //**********MEU CODIGO********
                    //DateTime horaInicialAula = Convert.ToDateTime(horaInicial[i].ToString());
                    //DateTime horaFinalAula = Convert.ToDateTime(horaFinal[i].ToString());


                    //Response.Write(" hora inicial " + horaInicial[i] + "<br>");
                    //Response.Write("hora inicial convertida: " + Convert.ToDateTime(drAula["dataInicial"].ToString()) + "<br>");
                    //Response.Write(" hora final " + horaFinal[i] + "<br>");
                    //Response.Write("hora final convertida: " + Convert.ToDateTime(drAula["dataFinal"].ToString()) + "<br>");

                    //Response.End();

                    //   DateTime DATAHORA = Convert.ToDateTime(Convert.ToDateTime(dtPesquisa).ToString("dd/MM/yyyy") +
                    //" " + Convert.ToDateTime(hrPesquisa).ToString("HH:mm"));

                    //**********FINAL MEU CODIGO********

                    comprovante += "<table width=\"100%\">\n";
                    comprovante += "<tr><td width=\"100\"><img src=\"images/LogoSBF2.png\" style=\"height: 42px; width: 62px\" /></td>\n";
                    comprovante += "<td width=\"566\"><table width=\"446\" border=\"1\" bordercolor=\"" + cor + "\" style=\"color: " + cor + "\" >\n";
                    comprovante += "<tr><td width=\"436\" style=\"font-size: 14px\">\n";
                    comprovante += "<div align=\"center\">Comprovante de Aula</div>\n";
                    comprovante += "</td></tr>\n";
                    comprovante += "</table></td></tr>\n";
                    comprovante += "<tr><td colspan=\"2\"><u><span class=\"style1\" style=\"color: " + cor + "\"><strong>Referente à Aula</strong></span></u></td></tr>\n";
                    comprovante += "</table>\n";

                    comprovante += "<table width=\"100%\" height=\"20px\" border=\"2\" cellspacing=\"1\" bordercolor= " + cor + " style=\"\"font-size: 12px;\">\n";

                    comprovante += "<tr align=\"center\">\n";
                    comprovante += "<th width=\"134\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Data</th>\n";

                    comprovante += "<td width=\"-10\" style=\"font-family:Verdana; font-size:9px\">" + data + " </td>\n";

                    comprovante += "<th width=\"70\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Dia&nbsp;da&nbsp;Semana</th>\n";



                    comprovante += "<td width=\"-10\" style=\"font-family:Verdana; font-size:9px\" >" + dtfi.GetDayName(date.DayOfWeek) + "</td>\n";

                    comprovante += "<th width=\"147\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Local</th>\n";

                    comprovante += "<td width=\"-10\" style=\"font-family:Verdana; font-size:9px\" >Sala de estudos</td>\n";

                    comprovante += "<th width=\"72\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Matéria</th>\n";

                    comprovante += "<td colspan=\"3\" style=\"font-family:Verdana; font-size:9px \" >" + drAula["disciplina"].ToString() + "</td>\n";

                    comprovante += "</tr>\n";

                    comprovante += "<tr align=\"center\">\n";
                    comprovante += "<th class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Hora&nbsp;de&nbsp;início&nbsp;e&nbsp;de&nbsp;término</th>\n";

                    comprovante += "<td colspan=\"3\" style=\"font-family:Verdana; font-size:9px \">" + horaInicial[i] + " às " + horaFinal[i] + "</td>\n";

                    comprovante += "<th class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Assuntos</th>\n";
                    comprovante += "<td colspan=\"5\" style=\"font-family:Verdana; font-size:9px\">" + drAula["descricaoAssuntoAula"].ToString() + "</td>\n";



                    comprovante += "</tr>\n";

                    comprovante += "<tr align=\"center\">\n";
                    comprovante += "<th class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Professor (a)</th>\n";

                    comprovante += "<td colspan=\"3\" style=\"font-family:Verdana; font-size:9px \" >" + drAula["Professor"].ToString() + "</td>\n";

                    comprovante += "<th class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Valor&nbsp;da&nbsp;Hora-aula&nbsp;Individual</th>\n";


                    string valorHora = drAula["valorHora"].ToString();

                    if (drAula["valorHora"].ToString() == "")
                    {
                        valorHora = "0";

                    }

                    comprovante += "<td style=\"font-family:Verdana; font-size:9px\" >totalHoraMudar</td>\n";

                    //comprovante += "<td style=\"font-family:Verdana; font-size:9px\" >R$ " + vV.validar(valorHora) + "</td>\n";

                    comprovante += "<th class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Total&nbsp;de&nbsp;Horas</th>\n";

                    //comprovante += "<td style=\"font-family:Verdana; font-size:9px\" ></td>\n";

                    comprovante += "<td style=\"font-family:Verdana; font-size:9px\" >" + quantidadeAula + "</td>\n";

                    comprovante += "<th width=\"109\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact; font-size: 12px; font-family:Verdana; color: #FFFFFF\">Valor&nbsp;Total&nbsp;da&nbsp;Aula</th>\n";



                    comprovante += "<td style=\"font-family:Verdana; font-size:9px\" >totalTotalMudar</td>\n";


                    //comprovante += "<td style=\"font-family:Verdana; font-size:9px\" >R$ " + vV.validar((Convert.ToDouble(dif.TotalHours) * Convert.ToDouble(valorHora)).ToString()) + "</td>\n";






                    comprovante += "</tr>\n";

                    comprovante += "</table>\n";


                    comprovante += "<span><u><span class=\"style6\" style=\"color: " + cor + "\"><span style=\"color: " + cor + ";\">Referente ao aluno</span></span></u><br />\n";
                    comprovante += "<span class=\"style1\" style=\"color: " + cor + "\">Concordo com os termos que constam neste comprovante de aula.</span></span>\n";


                    comprovante += "<table width=\"100%\" border=\"1\" cellspacing=\"1\" bordercolor= " + cor + " style=\"font-size: 12px; font-family:Verdana;\">\n";
                    comprovante += "<tr style=\"font-size: 12px; color: #FFFFFF\">\n";
                    comprovante += "<th width=\"28\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact;\">N°</th>\n";
                    comprovante += "<th width=\"184\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact;\">Nome do Aluno</th>\n";
                    comprovante += "<th width=\"175\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact;\">Assinatura do Aluno</th>\n";
                    comprovante += "<th width=\"83\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact;\">Valor Unitário</th>\n";
                    comprovante += "<th width=\"69\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact;\">Total-Horas</th>\n";
                    comprovante += "<th width=\"99\" class\"" + classe + "\" style=\"background-color: " + cor + "; -webkit-print-color-adjust:exact;\">Valor Total</th>\n";
                    comprovante += "</tr>\n";

                    double horaValor = 0;
                    double horaValorTotalHora = 0;
                    double horaValorTotalTotal = 0;

                    if (drAula["codigoGrupo"].ToString() == "")
                    {
                        horaValor = pegarValorHora(false, data, drAula["codigoAluno"].ToString());

                        comprovante += "<tr>\n";
                        comprovante += "<td>1</td>\n";
                        comprovante += "<td style=\"font-family:Verdana; font-size:9px \">" + drAula["aluno"].ToString() + "</td>\n";
                        comprovante += "<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n";
                        comprovante += "<td style=\"font-family:Verdana; font-size:9px\" >R$ " + vV.validar(horaValor.ToString()) + "</td>\n";
                        comprovante += "<td>" + quantidadeAula + "</td>\n";
                        comprovante += "<td>R$ " + vV.validar((horaValor * quantidadeAula).ToString()) + "</td>\n";

                        comprovante += "</tr>\n";

                        horaValorTotalHora += horaValor;
                        horaValorTotalTotal += (horaValor * quantidadeAula);
                    }
                    else
                    {
                        horaValor = pegarValorHora(true, data, drAula["codigoGrupo"].ToString());

                        string grupo = "SELECT codigoAlunoGrupo, codigoGrupo, codigoAluno, ";
                        grupo += "(SELECT nomeAluno FROM aluno WHERE (codigoAluno = alunoGrupo.codigoAluno)) AS nomeAluno ";
                        grupo += " FROM alunoGrupo where codigoGrupo=" + drAula["codigoGrupo"].ToString();

                        SqlCommand cmdAulagrupo = new SqlCommand(grupo, sqlf);
                        SqlDataReader drAulagrupo = cmdAulagrupo.ExecuteReader();
                        int alunos = 1;
                        if (drAulagrupo.HasRows)
                        {
                            while (drAulagrupo.Read())
                            {
                                comprovante += "<tr>\n";
                                comprovante += "<td>" + alunos + "</td>\n";
                                comprovante += "<td style=\"font-family:Verdana; font-size:9px \">" + drAulagrupo["nomeAluno"].ToString() + "</td>\n";
                                comprovante += "<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n";
                                comprovante += "<td style=\"font-family:Verdana; font-size:9px\" >R$ " + vV.validar(horaValor.ToString()) + "</td>\n";
                                comprovante += "<td>" + quantidadeAula + "</td>\n";
                                comprovante += "<td>R$ " + vV.validar((horaValor * quantidadeAula).ToString()) + "</td>\n";
                                comprovante += "</tr>\n";

                                alunos++;

                                horaValorTotalHora = horaValor;
                                horaValorTotalTotal += (horaValor * quantidadeAula);
                            }
                        }

                        drAulagrupo.Close();
                    }

                    comprovante += "</table>\n";
                    comprovante += "<u class=\"style1\" style=\"color: " + cor + "\">Referente ao professor:<br />Assinatura Professor(a)________________________________________________________</u><br />\n";
                    comprovante += "<img src=\"images/tesoura3.png\" width =\"15\" height=\"20\" style=\" margin-bottom: -6px; \" ";
                    comprovante += "<p>---------------------------------------------------------------------------------------------------------------------------------</p> </br> ";

                    comprovante = comprovante.Replace("totalHoraMudar", vV.validar(horaValorTotalHora.ToString()));
                    comprovante = comprovante.Replace("totalTotalMudar", vV.validar(horaValorTotalTotal.ToString()));

                }

                drAula.Close();

                if (cont == 1)
                {
                    comprovante += "</div>";
                }
                cont++;

                if (cont == 4)
                {
                    cont = 1;
                }

                i++;
            }
            sqlc.Close();
            sqlf.Close();

            Panel1.Controls.Add(new LiteralControl(comprovante));
        }

        double pegarValorHora(bool grupo, string data, string codigo)
        {
            double valor = 0;
            string selectAula = "";

            SqlConnection sqlf = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlf.Open();

            if (grupo)
            {
                selectAula = "SELECT a.codigoAula, a.codigoProfessor, a.codigoTipoAula, a.dataAula, a.meiaHora, ";
                selectAula += "(select nomeProfessor from professor where a.codigoProfessor = professor.codigoProfessor) as nomeProfessor, ";

                selectAula += "(select nomeGrupo from Grupo where a.codigoGrupo = Grupo.codigoGrupo) as nomeGrupo, ";

                selectAula += "a.codigoGrupo, a.codigoGrupo, a.dataAula, a.horaInicialAula, a.horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula, ";
                selectAula += "a.aulaRealizada, a.observacoesAula, a.valorHora, a.pago, a.aulaCancelada, a.valorPago, ";
                selectAula += "a.valorTotalSemDesconto, a.valorTotalComDesconto, a.codigoAnoLetivo, a.falta, a.meiaHora, ";

                selectAula += "(select v.aulaValor from valorAula v ";
                selectAula += "where convert(datetime, '" + data + "' ,103) BETWEEN convert(datetime, v.dataInicial,103) ";
                selectAula += "AND convert(datetime, v.dataFinal,103) and a.codigoTipoAula = v.codigoTipoAula) as valorAula, ";

                selectAula += "(select v.valorValorEspecialGrupo from valorEspecialGrupo v ";
                selectAula += "where convert(datetime, '" + data + "' ,103) BETWEEN convert(datetime, v.dataInicialValorEspecialGrupo,103) ";
                selectAula += "AND convert(datetime, v.dataFinalValorEspecialGrupo,103) and a.codigoGrupo = v.codigoGrupo) as valorAulaEspecial ";

                selectAula += "FROM aula a ";
                selectAula += "WHERE a.codigoGrupo = " + codigo + " and a.aulaRealizada = 1 and a.dataAula = '" + data + "' ";
                selectAula += " order by convert(datetime,a.dataAula,103) desc, CONVERT(INT, SUBSTRING(a.horaInicialAula,1 , 2))  ";

                SqlCommand cmdPrincipal = new SqlCommand(selectAula, sqlf);
                SqlDataReader drPrincipal = cmdPrincipal.ExecuteReader();

                if (drPrincipal.HasRows)
                {
                    drPrincipal.Read();
                    if (drPrincipal["valorAulaEspecial"].ToString().Replace(" ", "") != "")
                    {
                        try
                        {
                            if (Convert.ToBoolean(drPrincipal["meiaHora"]))
                            {
                                valor = Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString());
                            }
                            else
                            {
                                valor = Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString());
                            }
                        }
                        catch
                        {
                        }
                    }
                    else
                    {
                        try
                        {
                            if (Convert.ToBoolean(drPrincipal["meiaHora"]))
                            {
                                valor = Convert.ToDouble(drPrincipal["valorAula"].ToString());
                            }
                            else
                            {
                                valor = Convert.ToDouble(drPrincipal["valorAula"].ToString());
                            }
                        }
                        catch
                        {
                        }
                    }
                }

                drPrincipal.Close();
            }
            else
            {
                selectAula = "SELECT a.codigoAula, a.codigoProfessor, a.codigoTipoAula, a.dataAula, ";
                selectAula += "(select nomeProfessor from professor where a.codigoProfessor = professor.codigoProfessor) as nomeProfessor, ";

                selectAula += "(select nomeAluno from Aluno where a.codigoAluno = Aluno.codigoAluno) as nomeAluno, ";

                selectAula += "a.codigoAluno, a.codigoAluno, a.dataAula, a.horaInicialAula, a.horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula, ";
                selectAula += "a.aulaRealizada, a.observacoesAula, a.valorHora, a.pago, a.aulaCancelada, a.valorPago, ";
                selectAula += "a.valorTotalSemDesconto, a.valorTotalComDesconto, a.codigoAnoLetivo, a.falta, a.meiaHora, ";

                selectAula += "(select v.aulaValor from valorAula v ";
                selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicial,103) ";
                selectAula += "AND convert(datetime, v.dataFinal,103) and a.codigoTipoAula = v.codigoTipoAula) as valorAula, ";

                selectAula += "(select v.valorValorEspecialAluno from valorEspecialAluno v ";
                selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicialValorEspecialAluno,103) ";
                selectAula += "AND convert(datetime, v.dataFinalValorEspecialAluno,103) and a.codigoAluno = v.codigoAluno) as valorAulaEspecial ";

                selectAula += "FROM aula a ";
                selectAula += "WHERE a.codigoAluno = " + codigo + " and a.aulaRealizada = 1 and a.dataAula = '" + data + "' ";
                selectAula += " order by convert(datetime,a.dataAula,103) desc, CONVERT(INT, SUBSTRING(a.horaInicialAula,1 , 2))  ";

                SqlCommand cmdPrincipal = new SqlCommand(selectAula, sqlf);
                SqlDataReader drPrincipal = cmdPrincipal.ExecuteReader();

                if (drPrincipal.HasRows)
                {
                    while (drPrincipal.Read())
                    {
                        if (drPrincipal["valorAulaEspecial"].ToString().Replace(" ", "") != "")
                        {
                            try
                            {
                                if (Convert.ToBoolean(drPrincipal["meiaHora"]))
                                {
                                    valor = Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString());
                                }
                                else
                                {
                                    valor = Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString());
                                }
                            }
                            catch
                            {
                            }
                        }
                        else
                        {
                            try
                            {
                                if (Convert.ToBoolean(drPrincipal["meiaHora"]))
                                {
                                    valor = Convert.ToDouble(drPrincipal["valorAula"].ToString());
                                }
                                else
                                {
                                    valor = Convert.ToDouble(drPrincipal["valorAula"].ToString());
                                }
                            }
                            catch
                            {
                            }
                        }
                    }
                }

                drPrincipal.Close();
            }

            sqlf.Close();

            return valor;
        }

        void ordernar()
        {
            int i, j;
            DateTime horaI;

            string horaF, professor, aluno, grupo, disciplina, codigoAula;

            for (i = 1; i < horaInicialT.Count; i++)
            {
                horaI = Convert.ToDateTime(horaInicialT[i]);
                horaF = horaFinalT[i];
                professor = professorT[i];
                aluno = alunoT[i];
                grupo = grupoT[i];
                disciplina = disciplinaT[i];
                codigoAula = codigoAulaT[i];

                j = i;
                while ((j > 0) && (Convert.ToDateTime(horaInicialT[j - 1]) > horaI))
                {
                    horaInicialT[j] = horaInicialT[j - 1];
                    horaFinalT[j] = horaFinalT[j - 1];
                    professorT[j] = professorT[j - 1];
                    alunoT[j] = alunoT[j - 1];
                    grupoT[j] = grupoT[j - 1];
                    disciplinaT[j] = disciplinaT[j - 1];
                    codigoAulaT[j] = codigoAulaT[j - 1];
                    j = j - 1;
                }
                horaInicialT[j] = horaI.ToString("HH:mm");

                horaFinalT[j] = horaF;
                professorT[j] = professor;
                alunoT[j] = aluno;
                grupoT[j] = grupo;
                disciplinaT[j] = disciplina;
                codigoAulaT[j] = codigoAula;
            }
            //return horaInicial;
        }
    }
}
